package com.tjtt.tdm.codeReview.dao.impl;


import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.hibernate.Query;

import com.tjtt.tdm.base.BaseHibernateDao;
import com.tjtt.tdm.base.PageResult;
import com.tjtt.tdm.base.StringUtil;
import com.tjtt.tdm.codeReview.dao.IItemDao;

public class ItemDaoImpl extends BaseHibernateDao implements IItemDao{
	
	/**
	 * 
	 * 描述：根据taskId查询样品列表
	 * @author wangxd
	 * @date Jun 30, 2015 4:20:46 PM
	 * @return PageResult
	 * @throw
	 */
	public PageResult querySampleByTaskId(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM GWM_SAMPLE T WHERE 1=1 ");
		queryString.append("select T.ID,T.T_SAMPLENAME,T.T_SAMPLECOUNT ,T_SAMPLENO,T.T_PICI,F.T_CHEXINGNAME,T.T_CHEXINGBIANMA,T.T_MANUFACTURER,T.T_SONGYANGDATE,T.T_SAMPLECHULI,A.FILE_NAME,T.T_ANNEX from GWM_SAMPLE T LEFT JOIN GWM_APPLICATIONMODE F ON T.T_YINGYONGCHEXING = F.ID LEFT JOIN TDM_ANNEX A ON T.T_ANNEX = A.ID WHERE 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			queryCountString.append("and t.T_TASKID ='" + filterMap.get("taskId")+"'");
			queryString.append("and t.T_TASKID ='" + filterMap.get("taskId")+"'");
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		} 
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	/**
	 * 
	 * 描述：根据taskId查询整车样品列表
	 * @author wangxj
	 * @date Jun 30, 2015 4:20:46 PM
	 * @return PageResult
	 * @throw
	 */
	public PageResult queryZCSampleByTaskId(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM GWM_SAMPLE T WHERE 1=1 ");
		queryString.append("select T.ID,T.t_Samplename,T.T_SAMPLECOUNT ,T_SAMPLENO,T.T_PICI,F.T_CHEXINGNAME,T.T_CHEXINGBIANMA,T.T_MANUFACTURER," +
				"T.T_SONGYANGDATE,T.T_SAMPLECHULI,A.FILE_NAME,T.T_ANNEX,T.VIN,T.CHELIANGXINGHAO,T.CHENGYUAN,T.ZHENGBEIZHILIANG,T.ZUIGAOCHE,T.LICHENG,T.DONGLILEIXING," +
				"T.CHELIANGNAME from GWM_SAMPLE T LEFT JOIN GWM_APPLICATIONMODE F ON T.T_YINGYONGCHEXING = F.ID LEFT JOIN TDM_ANNEX A ON T.T_ANNEX = A.ID WHERE 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			queryCountString.append("and t.T_TASKID ='" + filterMap.get("taskId")+"'");
			queryString.append("and t.T_TASKID ='" + filterMap.get("taskId")+"'");
		} 
		queryString.append(" order by t.id desc ");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
		
	
	/**
	 * 
	 * 描述：根据taskId查询检测项目列表
	 * @author wangxd
	 * @date Jun 30, 2015 4:20:46 PM
	 * @return PageResult
	 * @throw
	 */
	public PageResult queryItemByTaskId(Map filterMap) { 
		
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM ( ");
		//选择检测项目列表 查询不同的检测项目
		if ("0".equals(filterMap.get("type"))){
			queryString.append("select distinct T.T_ITEMID ,T.T_ITEMNAME,T.T_BIAOZHUNWEITUO,T.T_BAOGAOTYPE,T.T_BGYAOQIU,A.FILE_NAME,T.T_ANNEX,case when t.t_jiancebiaozhun is null then '0' else '1' end as bzcount,t.t_jiancemoney,t.t_jiancebiaozhun from GWM_ITEM_FLOW T LEFT JOIN TDM_ANNEX A ON T.T_ANNEX = A.ID WHERE 1=1 and t.T_TASKID =" + filterMap.get("taskId"));
		}
		else if("1".equals(filterMap.get("type"))) {
			queryString.append("select  T.ID ,T.T_ITEMNAME,T.T_BIAOZHUNWEITUO,T.T_BAOGAOTYPE,T.T_BGYAOQIU,A.FILE_NAME,T.T_ANNEX,case when t.t_jiancebiaozhun is null then '0' else '1' end as bzcount,t.t_jiancemoney,t.t_jiancebiaozhun from GWM_ITEM_FLOW T LEFT JOIN TDM_ANNEX A ON T.T_ANNEX = A.ID WHERE 1=1 and t.T_TASKID =" + filterMap.get("taskId"));
		}
		else{
			
			queryString.append("select distinct T.T_ITEMID ,T.T_ITEMNAME,T.T_BIAOZHUNWEITUO,T.T_BAOGAOTYPE,T.T_BGYAOQIU,A.FILE_NAME,T.T_ANNEX,case when t.t_jiancebiaozhun is null then '0' else '1' end as bzcount,t.t_jiancemoney,t.t_jiancebiaozhun from GWM_ITEM_FLOW T LEFT JOIN TDM_ANNEX A ON T.T_ANNEX = A.ID WHERE t.t_taskid =" + filterMap.get("taskId"));
		}
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			if (!StringUtil.isMissing((String)filterMap.get("type"))){
				queryString.append(" and t.T_ITEMTYPE =" + filterMap.get("type"));
			}
			
			if (!StringUtil.isMissing((String)filterMap.get("itemFlowId"))){
				queryString.append(" and T.ID in (" + filterMap.get("itemFlowId")+")");
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		}
		queryCountString.append(queryString.toString()+")");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	/**
	 * 
	 * 描述：选择检测项目的左侧列表
	 * @author wangxd
	 * @date Jul 3, 2015 4:09:25 PM
	 * @return PageResult
	 * @throw
	 */
	public PageResult queryItemFromTable(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM (");
		if("0".equals(filterMap.get("type"))){   //检测项目左侧列表
	    
			queryString.append("select distinct  case when (t.t_jiancebumen is null or t.t_jiaceleibie is null) then t.id else (select a.id   from gwm_item a where t.t_itemname = a.t_itemname and t.t_jiancedanwei = a.t_jiancedanwei and t.t_jiancebumen = a.t_jiancebumen and t.t_jiaceleibie = a.t_jiaceleibie  and rownum = 1) end NEWID, t.T_ITEMNAME,t.T_JIANCEDANWEI_NAME ,t.T_JIANCEDANWEI,t.T_GONGSIDAIMA,t.T_JIANCEDIDIAN,t.T_JIANCEBUMEN,t.T_JIACELEIBIE,'' a1,'' a2   FROM  GWM_ITEM t   WHERE 1=1 ");
		
		}
		else if("1".equals(filterMap.get("type"))){ //检测项目右侧列表
			queryString.append("select distinct T.T_ITEMID,T.T_ITEMNAME,T.T_JIANCEDANWEI_NAME,t.T_JIANCEDANWEI,t.T_GONGSIDAIMA,T.T_JIANCEDIDIAN,T.T_JIANCEBUMEN,T.T_JIACELEIBIE,T.T_JIANCEMONEY,T.T_JIANCEBIAOZHUN  from GWM_ITEM_FLOW T  WHERE   T.T_ITEMTYPE=0 ");
		}
		
	   if (filterMap!= null && !filterMap.isEmpty()) {
			
		   if("0".equals(filterMap.get("type"))){
			   if (!StringUtil.isMissing((String)filterMap.get("deptId"))){
					queryString.append("and t.T_JIANCEDANWEI ='" + filterMap.get("deptId")+"'");
				}
			   if (!StringUtil.isMissing((String)filterMap.get("taskId"))){
                    queryString.append(" and t.id not in (select a.id from gwm_item a left join gwm_item a1 on a.t_itemname = a1.t_itemname  and a.t_jiancedanwei = a1.t_jiancedanwei and a.t_jiaceleibie = a1.t_jiaceleibie and a.t_jiancebumen = a1.t_jiancebumen where a1.id in (select f.t_itemid from GWM_ITEM_FLOW f where f.T_TASKID = "+filterMap.get("taskId")+"))");
			   
			   }
				if (!StringUtil.isMissing((String)filterMap.get("itemName"))){
					queryString.append("and t.T_ITEMNAME like '%" + filterMap.get("itemName")+"%'");
				}
				if (!StringUtil.isMissing((String)filterMap.get("jiancedanwei"))){
					queryString.append("and T.T_JIANCEDANWEI_NAME like '%" + filterMap.get("jiancedanwei")+"%'");
				}
				if (!StringUtil.isMissing((String)filterMap.get("danweidaima"))){
					queryString.append("and t.T_GONGSIDAIMA like '%" + filterMap.get("danweidaima")+"%'");
				}
				if (!StringUtil.isMissing((String)filterMap.get("jiancedidian"))){
					queryString.append("and t.T_JIANCEDIDIAN like '" + filterMap.get("jiancedidian")+"%'");
				}
				if (!StringUtil.isMissing((String)filterMap.get("jiancebumen"))){
					queryString.append("and t.T_JIANCEBUMEN like '%" + filterMap.get("jiancebumen")+"%'");
				}
				if(!StringUtil.isMissing((String)filterMap.get("taskleibie")) && "1".equals((String)filterMap.get("taskleibie"))){
					queryString.append("and t.T_JIACELEIBIE like '%整车可靠性%'");
				}
				if (!StringUtil.isMissing((String)filterMap.get("leibie"))){
					queryString.append("and t.T_JIACELEIBIE like '%" + filterMap.get("leibie")+"%'");
				}
				
		   }
		   else if("1".equals(filterMap.get("type"))){
			   if (!StringUtil.isMissing((String)filterMap.get("taskId"))){
					queryString.append("and T.T_TASKID ='" + filterMap.get("taskId")+"'");
				}
		   }
		    queryCountString.append(queryString.toString()+")");
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		} 
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	/**
	 * 
	 * 描述：根据检测项目查询对应的检测标准
	 * @author wangxd
	 * @date Jul 3, 2015 4:29:07 PM
	 * @return PageResult
	 * @throw
	 */
	public PageResult queryItemBiaozhunByItemId(Map filterMap){
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		String filedName ="";
		
		queryCountString.append("select count(*) from (");
		queryString.append("select distinct  (select  wmsys.wm_concat(a.id) from gwm_item a where t.t_itemname = a.t_itemname and t.t_jiancedanwei = a.t_jiancedanwei and t.t_jiancebiaozhun = a.t_jiancebiaozhun  ) NEWID, T.T_JIANCEBIAOZHUN,T.T_JIANCEDANWEI_NAME,T.T_JIANCEMONEY from gwm_item T left join gwm_item t1 on t.t_itemname = t1.t_itemname and t.t_jiancedanwei = t1.t_jiancedanwei and t.t_jiancebumen = t1.t_jiancebumen and  t.t_jiaceleibie=t1.t_jiaceleibie WHERE t1.id="+filterMap.get("itemId")+" and t.t_jiancebiaozhun is not null ");
		
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			
			if(!StringUtil.isMissing((String)filterMap.get("type"))&&"0".equals(filterMap.get("type"))){
				
				//常规检测流程 调度审核委托单中 修改检测标准
				if (!StringUtil.isMissing((String)filterMap.get("jiancebiaozhun"))){
					queryString.append(" and t.T_JIANCEBIAOZHUN like '%" + filterMap.get("jiancebiaozhun")+"%'");
				}
				
				queryString.append(" and  t.t_jiancebiaozhun not in (select t1.t_jiancebiaozhun  from gwm_item_flow t1 where t1.t_itemid ="+filterMap.get("itemId")+" and t1.t_taskid="+filterMap.get("taskId")+" and t1.t_jiancebiaozhun is not null )" ) ;
			}
			
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		}
		queryCountString.append(queryString.toString()+")");
		
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	public List getDatabySql(String sql) {
		Query query = getSession().createSQLQuery(sql);
		List l =query.list(); 
		return l;
	}
	/**
	 * 
	 * 描述：常规检测流程--->项目分配中 样品列表
	 * @author wangxd
	 * @date Jul 14, 2015 4:17:57 PM
	 * @return void
	 * @throw
	 */
	public PageResult queryItemFenpeiSampleno(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM GWM_SAMPLENOFLOW T LEFT JOIN GWM_SAMPLE S ON T.T_SAMPLEID= S.ID  WHERE 1=1 ");
		queryString.append("select T.ID,T.T_SAMPLENO,S.T_SAMPLENAME,S.T_SONGYANGDATE,A.FILE_NAME,S.T_ANNEX FROM GWM_SAMPLENOFLOW T LEFT JOIN GWM_SAMPLE S ON T.T_SAMPLEID= S.ID LEFT JOIN TDM_ANNEX A ON S.T_ANNEX = A.ID WHERE 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			queryCountString.append("and s.T_TASKID ='" + filterMap.get("taskId")+"'");
			queryString.append("and s.T_TASKID ='" + filterMap.get("taskId")+"'");
			
			if(!"".equals(filterMap.get("sampleno"))){
				queryCountString.append("and T.T_SAMPLENO  in (" + filterMap.get("sampleno")+")");
				queryString.append("and T.T_SAMPLENO in (" + filterMap.get("sampleno")+")");
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		} 
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	/**
	 * 
	 * 描述：常规检测流程--->项目分配中 项目列表
	 * @author wangxd
	 * @date Jul 14, 2015 4:17:57 PM
	 * @return void
	 * @throw
	 */
	public PageResult queryItemFenpeiItem(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM GWM_ITEM_FLOW T WHERE 1=1 ");
		queryString.append("select T.ID,T.T_ITEMNAME,(SELECT  WMSYS.WM_CONCAT( F.T_JIANCEBIAOZHUN)  FROM GWM_JIANCEBZ_FLOW F WHERE F.T_ITEMID = T.ID ) JIANCEBIAOZHUN,A.FILE_NAME,T.T_ANNEX FROM GWM_ITEM_FLOW T  LEFT JOIN TDM_ANNEX A ON T.T_ANNEX = A.ID  WHERE 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			queryCountString.append("and T.T_TASKID ='" + filterMap.get("taskId")+"'");
			queryString.append("and T.T_TASKID ='" + filterMap.get("taskId")+"'");
			
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		} 
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	/**
	 * 
	 * 描述：常规检测流程--->项目分配中 样品/项目列表
	 * @author wangxd
	 * @date Jul 14, 2015 4:17:57 PM
	 * @return void
	 * @throw
	 */
	public PageResult queryItemSampleByTaskId(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM ( ");
		if ("0".equals(filterMap.get("type"))){
			queryString.append("select distinct T.T_SAMPLENO||'_'||T.T_GMW_ITEM_ID , T.T_SAMPLENO,T.T_ITEMNAME,'',O.ORG_NAME,T.T_DEPTLEADERNAME,T.T_JIACELEIBIE,T.T_JIANCEBIAOZHUN,T.T_JIANCEYUANNAME,SUBSTR(T.T_YSJLFILEPATH,INSTR(T_YSJLFILEPATH,'/',-1,1)+1,20),CASE WHEN T_SFDIANZIQIANM =0 THEN '是' ELSE '否' END ,T_BGFILEPATH,I.T_BAOGAOTYPE,T1.FILE_NAME SAMPLE_FILE_NAME,T2.FILE_NAME ITEM_FILE_NAME,S.T_ANNEX T_ANNEX,I.T_ANNEX IT_ANNEX,f.main_id," +
					"decode((select count(*) from TDM_ATTACHMENT a  left join flow_task f1  on a.code_id = f1.main_id  where f1.id=t.t_taskid and a.column_name = to_char(t.id)),'0','否','是'),T_REPORTSHIBIEH,CASE WHEN T_REPORTSFQIANMING =0 THEN '是' ELSE '否' END ,Y.FILE_NAME AS YSJLNAME,R.REPORT_NAME AS BGFILENAME " +
					" FROM GWM_ITEM_SAMPLE T LEFT JOIN FILE_MODE Y ON T.T_FILEMODEID = Y.ID LEFT JOIN REPORT_MODE R ON T.T_BGFILEMODEID = R.ID  LEFT JOIN GWM_SAMPLE S ON T.T_SAMPLEID = S.ID LEFT JOIN TDM_ANNEX T1 ON S.T_ANNEX = T1.ID  LEFT JOIN GWM_ITEM_FLOW I ON T.T_ITEMID = I.ID LEFT JOIN TDM_ANNEX T2 ON I.T_ANNEX = T2.ID LEFT JOIN SYS_ORGANIZATION O on T.T_DEPTID = O.ORG_ID" +
					" left join flow_task f on f.id = t.t_taskid  WHERE 1=1 and T.T_TASKINFOID =" + filterMap.get("flowtaskinfoid") );
		}else{
		queryString.append("select T.ID,T.T_SAMPLENO,T.T_ITEMNAME,T.T_JIANCECANSHU,O.ORG_NAME,T.T_DEPTLEADERNAME,T.T_JIACELEIBIE,T.T_JIANCEBIAOZHUN,T.T_JIANCEYUANNAME,SUBSTR(T.T_YSJLFILEPATH,INSTR(T_YSJLFILEPATH,'/',-1,1)+1,20),CASE WHEN T_SFDIANZIQIANM =0 THEN '是' ELSE '否' END ,T_BGFILEPATH,I.T_BAOGAOTYPE,T1.FILE_NAME SAMPLE_FILE_NAME,T2.FILE_NAME ITEM_FILE_NAME,S.T_ANNEX T_ANNEX,I.T_ANNEX IT_ANNEX,f.main_id," +
				"decode((select count(*) from TDM_ATTACHMENT a  left join flow_task f1  on a.code_id = f1.main_id  where f1.id=t.t_taskid and a.column_name = to_char(t.id)),'0','否','是'),T_REPORTSHIBIEH,CASE WHEN T_REPORTSFQIANMING =0 THEN '是' ELSE '否' END,Y.FILE_NAME AS YSJLNAME,R.REPORT_NAME AS BGFILENAME " +
				" FROM GWM_ITEM_SAMPLE T LEFT JOIN FILE_MODE Y ON T.T_FILEMODEID = Y.ID LEFT JOIN REPORT_MODE R ON T.T_BGFILEMODEID = R.ID LEFT JOIN GWM_SAMPLE S ON T.T_SAMPLEID = S.ID LEFT JOIN TDM_ANNEX T1 ON S.T_ANNEX = T1.ID  LEFT JOIN GWM_ITEM_FLOW I ON T.T_ITEMID = I.ID LEFT JOIN TDM_ANNEX T2 ON I.T_ANNEX = T2.ID LEFT JOIN SYS_ORGANIZATION O on T.T_DEPTID = O.ORG_ID" +
				" left join flow_task f on f.id = t.t_taskid  WHERE t_sfdelete is null ");
		}
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			queryString.append(" and T.T_TASKID ='" + filterMap.get("taskId")+"'");
			if (!StringUtil.isMissing((String)filterMap.get("stateId"))){
				//科室任务分配
				if("ksrwfp".equals(filterMap.get("stateId")) || "proc1".equals(filterMap.get("stateId"))){
					queryString.append(" and T.T_DEPTLEADERID =" + filterMap.get("userId"));
				}
				//试验任务接受、填写原始记录
				else if("syrwjs".equals(filterMap.get("stateId"))||"txysjl".equals(filterMap.get("stateId")) || "proc2".equals(filterMap.get("stateId"))
						|| "proc3".equals(filterMap.get("stateId")) || "proc8".equals(filterMap.get("stateId"))){
					queryString.append(" and T.T_JIANCEYUANID like '%," + filterMap.get("userId")+",%'");
				}
				
				//报告与原始记录发放
				if("bgysjlff".equals(filterMap.get("stateId"))){
					queryString.append(" and T.T_REPORTSFTJ =1 ");
					
				}else{
					if(null!=filterMap.get("flowtaskinfoid")&&!"".equals(filterMap.get("flowtaskinfoid"))){
						queryString.append(" and T.T_TASKINFOID =" + filterMap.get("flowtaskinfoid"));
					}
					//报告是否提交
					if(null!=filterMap.get("reportsftj")&&"0".equals(filterMap.get("reportsftj"))){
						queryString.append(" and T.T_REPORTSFTJ =0 ");
					}else{
						queryString.append( " and T.T_REPORTSFTJ  is null ");
					}
					
				}
				
				
			}
			queryCountString.append(queryString.toString() +" )");
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		} 
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	public String queryDeptUsers(String deptid) {
		String sql = "select s.userid,s.username " +
					  " from sys_users s " +
					  " where s.org_id in (select t.org_id " +
					  " from sys_organization t " +
					  " connect by prior t.org_id = t.org_parent_id " +
					  " start with t.org_id = "+deptid+") and s.del_flag=1";
		Query query = getSession().createSQLQuery(sql);
		List list = query.list();
		String result="[";
		for (int i = 0; i < list.size(); i++) {
			Object[] objs = (Object[])list.get(i);
			String id = String.valueOf(objs[0]);
			String name = String.valueOf(objs[1]);
			result=result+"{\"id\":\""+id+"\",\"cloumnName\":\""+name+"\"},";
		}
		if(!"[".equals(result)){
			result=result.substring(0,result.length()-1);
		}
		result=result+"]";
		return result;
	}
	/**
	 * 我的请求---检测项目
	 * wxj
	 */
	public PageResult queryMyItemSample(Map filterMap){
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryString.append("select n.* from (select distinct t.t_bgfilepath as filepath,"+
                "'1' as leibie,"+
                "t.t_reportshibieh as shibiehao,"+
//                "t.t_jiaceleibie,"+
                "(select wmsys.wm_concat( distinct tt.t_itemname) from GWM_ITEM_SAMPLE tt where tt.t_bgfilepath=t.t_bgfilepath) as t_itemname,"+
                "t.t_taskid,"+
                "t.t_jianceyuanname,"+
                "(select t2.id from  GWM_ITEM_SAMPLE t2 where t2.t_bgfilepath=t.t_bgfilepath and rownum=1) as id"+
                " from GWM_ITEM_SAMPLE t "+
                " where t.t_reportsftj = 0 "+
                " and t.t_bgfilepath is not null ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			queryString.append("and T.T_TASKID ='" + filterMap.get("taskId")+"'");
		}
		queryString.append(" union "+
                " select distinct t.t_ysjlfilepath as filepath,"+
                "'0' as leibie,"+
                "t.T_YSJLSHIBIEH as shibiehao,"+
//                "t.t_jiaceleibie,"+
                "(select wmsys.wm_concat( distinct tt.t_itemname) from GWM_ITEM_SAMPLE tt where tt.t_ysjlfilepath=t.t_ysjlfilepath) as t_itemname,"+
                "t.t_taskid,"+
                "t.t_jianceyuanname,"+
                "(select t2.id from  GWM_ITEM_SAMPLE t2 where t2.t_ysjlfilepath=t.t_ysjlfilepath and rownum=1) as id"+
                " from GWM_ITEM_SAMPLE t "+
                " where t.t_reportsftj = 0 "+
                " and t.t_bgfilepath is null "+
                " and t.t_ysjlfilepath is not null ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			queryString.append("and T.T_TASKID ='" + filterMap.get("taskId")+"'");
		}
		queryString.append(") n where 1=1");
		queryCountString.append("select count(*) from ("+queryString+") ");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	public String executeSql(String sql) {
		String id = "";
		Query query = getSession().createSQLQuery(sql);
		int count = query.executeUpdate();
		if(count>0){
			id = count+"";
		}
		return id;
	}
	/**
	 * 我的请求---研发报告
	 * wxj
	 */
	public PageResult queryMyItemSampleYF(Map filterMap){
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryString.append("select t.baogao as filepath,'1' as leibie,t.jiancebaogaoshibieno as shibiehao,'' as t_itemname,f.id as t_taskid,t.renwuyuanname as t_jianceyuanname,t.id from GWM_YANFAJIANCE t left join flow_task f on f.main_id=t.id and f.table_name='GWM_YANFAJIANCE' where f.flow_state_id='end' ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			queryString.append("and f.id ='" + filterMap.get("taskId")+"'");
		}
		queryCountString.append("select count(*) from ("+queryString+") ");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	/**
	 * 
	 * 描述：检测项目管理 ：查询列表
	 * @author wangxd
	 * @date Sep 24, 2015 8:44:45 AM
	 * @return PageResult
	 * @throw
	 */
	public PageResult queryGwmItemList(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM  (");
		
		queryString.append("select distinct  t.T_ITEMNAME,t.T_JIANCEDANWEI_NAME,t.t_jiancedanwei,t.T_GONGSIDAIMA,t.T_JIANCEDIDIAN,t.T_JIANCEBUMEN,t.T_JIACELEIBIE, case when (t.t_jiancebumen is null or t.t_jiaceleibie is null) then t.id else (select a.id  from gwm_item a where t.t_itemname = a.t_itemname and t.t_jiancedanwei = a.t_jiancedanwei and t.t_jiancebumen = a.t_jiancebumen and t.t_jiaceleibie = a.t_jiaceleibie and rownum = 1) end NEWID FROM  GWM_ITEM t   WHERE 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			if(null!=filterMap.get("itemname")&&!"".equals(filterMap.get("itemname"))){
				queryString.append(" and t.T_ITEMNAME like '%" + filterMap.get("itemname")+"%'");
			}
			if(null!=filterMap.get("danwei")&&!"".equals(filterMap.get("danwei"))){
				queryString.append(" and t.T_JIANCEDANWEI_NAME like '%" + filterMap.get("danwei")+"%'");
			}
			if(null!=filterMap.get("didian")&&!"".equals(filterMap.get("didian"))){
				queryString.append(" and t.T_JIANCEDIDIAN like '%" + filterMap.get("didian")+"%'");
			}
			if(null!=filterMap.get("bumen")&&!"".equals(filterMap.get("bumen"))){
				queryString.append(" and t.T_JIANCEBUMEN like '%" + filterMap.get("bumen")+"%'");
			}
			if(null!=filterMap.get("leibie")&&!"".equals(filterMap.get("leibie"))){
				queryString.append(" and t.T_JIACELEIBIE like '%" + filterMap.get("leibie")+"%'");
			}
			
			queryCountString.append(queryString.toString()+")");
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		} 
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	/**
	 * 
	 * 描述：查询已删除检测参数列表
	 * @author wangxd
	 * @date Oct 13, 2015 10:23:53 PM
	 * @return PageResult
	 * @throw
	 */
	public PageResult queryJiancecanshuList(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM ( ");
		queryString.append("select T.ID,T.T_SAMPLENO,T.T_ITEMNAME,T.T_JIANCECANSHU,O.ORG_NAME,T.T_DEPTLEADERNAME,T.T_JIACELEIBIE,T.T_JIANCEBIAOZHUN,T.T_JIANCEYUANNAME" +
				" FROM GWM_ITEM_SAMPLE T LEFT JOIN SYS_ORGANIZATION O on T.T_DEPTID = O.ORG_ID" +
				" left join flow_task f on f.id = t.t_taskid  WHERE t_sfdelete =0 and T.T_TASKINFOID ='" + filterMap.get("taskInfoId")+"' ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			queryCountString.append(queryString.toString() +" )");
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		} 
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	/**
	 * 
	 * 描述：检测项目管理中 --
	 * @author wangxd
	 * @date Oct 15, 2015 10:21:24 AM
	 * @return PageResult
	 * @throw
	 */
	public PageResult queryItemcanshuList(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM ( ");
		queryString.append("select rownum,t.t_itemname,t.t_jiancedanwei,t.t_jiaceleibie,t.t_jiancecanshu from (select distinct t1.t_itemname,t1.t_jiancedanwei,t1.t_jiaceleibie,t1.t_jiancecanshu  from gwm_item t1 where t1.t_itemname='"+filterMap.get("itemname")+"' and t1.t_jiancedanwei='"+filterMap.get("danwei")+"' and t1.t_jiaceleibie='"+filterMap.get("leibie")+"' and t1.t_jiancecanshu is not null ) t ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		} 
		queryCountString.append(queryString.toString()+")");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
}
